package org.liuxw.main;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.liuxw.bean.Source;
import org.liuxw.bean.ColumnValue;
import org.liuxw.util.FmTemplate;

import java.io.File;
import java.io.IOException;
import java.util.*;

public class ReadExcelXlsx {

    private static final String SOURCE_TABLENAME = "源表名";
    private static final String DESTINATION_TABLENAME = "目的表名";
    private static final String SOURCE_DBNAME = "源数据库名称";
    private static final String DESTINATION_DBNAME = "目的数据库名称";
    private static final String RESOURCELIBRARY_RELATIVEPATH = "资源库相对路径";

    private static final String KEY_LIST = "键LIST";
    private static final String VALUE_LIST = "值LIST";

    private static final String UUID = "UUID()";

    FmTemplate fmTemplate = new FmTemplate();

    public void readData(String filePath) {
        OPCPackage pkg = null;
        try {
            pkg = OPCPackage.open(new File(filePath));
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(pkg);
            // 循环页
            for (XSSFSheet xssfSheet : xssfWorkbook) {
                int sheetCountNum = xssfSheet.getLastRowNum();
                if (sheetCountNum == 0) continue;

                Source source = new Source();

                boolean flagKey = false;

                boolean flagValue = false;

                Map<String, String> keyMap = new HashMap<String, String>();

                List<ColumnValue> columnValueList = new ArrayList<ColumnValue>();

                // 循环行
                for (int i = 0; i <= sheetCountNum; i++) {
                    XSSFRow xssfRow = xssfSheet.getRow(i);
                    if(xssfRow == null) continue;
                    // 循环单元格
                    for (Iterator it = xssfRow.cellIterator(); it.hasNext(); ) {
                        String cellStr = ((Cell) it.next()).getStringCellValue();
                        if (SOURCE_TABLENAME.equals(cellStr)) {
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setSourceDbtableName(cellStr);
                        } else if (DESTINATION_TABLENAME.equals(cellStr)) {
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setDestinationDbtableName(cellStr);
                        } else if (SOURCE_DBNAME.equals(cellStr)) {
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setSourceDbname(cellStr);
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setSourceDbtype(cellStr);
                        } else if (DESTINATION_DBNAME.equals(cellStr)) {
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setDestinationDbname(cellStr);
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setDestinationDbtype(cellStr);
                        } else if (RESOURCELIBRARY_RELATIVEPATH.equals(cellStr)) {
                            cellStr = ((Cell) it.next()).getStringCellValue();
                            source.setResourcelibraryRelativepath(cellStr);
                        } else if (KEY_LIST.equals(cellStr)) {
                            flagKey = true;
                            i++; // 跳一行
                            break;
                        } else if (VALUE_LIST.equals(cellStr)) {
                            flagKey = false;
                            flagValue = true;
                            i++; // 跳一行
                            break;
                        }

                        if (flagKey) {
                            String keyStr = cellStr;
                            String valueStr = ((Cell) it.next()).getStringCellValue();
                            keyMap.put(keyStr, valueStr);
                        }

                        if (flagValue) {
                            /*if ("".equals(cellStr.trim()) || cellStr == null) {
                                continue;
                            }*/
                            ColumnValue columnValue = new ColumnValue();
                            columnValue.setDestinationtableColumn(cellStr);
                            columnValue.setDestinationtableColumnType(((Cell) it.next()).getStringCellValue());
                            columnValue.setSourcetableColumn(((Cell) it.next()).getStringCellValue());
                            columnValue.setSourcetableColumnType(((Cell) it.next()).getStringCellValue());
                            // 判断是否主键
                            boolean isKey = keyMap.containsKey(cellStr);
                            if (isKey) {
                                columnValue.setIsKey(1);
                            } else {
                                columnValue.setIsKey(0);
                            }
                            columnValueList.add(columnValue);
                        }
                    }
                }

                String sourcetableSql = "";
                sourcetableSql += "select " + "\n";
                for (int i = 0; i < columnValueList.size(); i++) {
                    if (i == 0) {
                        sourcetableSql += "     \"" + columnValueList.get(i).getSourcetableColumn()
                                + "\" AS \"" + columnValueList.get(i).getDestinationtableColumn() + "\"\n";
                    } else {
                        sourcetableSql += "    ,\"" + columnValueList.get(i).getSourcetableColumn()
                                + "\" AS \"" + columnValueList.get(i).getDestinationtableColumn() + "\"\n";
                    }

                    if (UUID.equals(columnValueList.get(i).getSourcetableColumn())) {
                        columnValueList.get(i).setSourcetableColumn(columnValueList.get(i).getDestinationtableColumn());
                    }
                    if ("%".equals(columnValueList.get(i).getSourcetableColumn())) {
                        columnValueList.get(i).setSourcetableColumn(columnValueList.get(i).getDestinationtableColumn());
                    }
                }
                sourcetableSql += "    ,to_char(systimestamp,'YYYYMMDDHH24miss')" + " AS IMP_TIME" + "\n";
                sourcetableSql += "    ,NULL" + " AS ENTER_DEPT_ID" + "\n";
                sourcetableSql += "    ,'1'" + " AS IS_ACTIVE" + "\n";
                sourcetableSql += "    ,'admin'" + " AS CREATOR" + "\n";
                sourcetableSql += "    ,to_char(systimestamp,'YYYYMMDDHH24miss')" + " AS CREATE_TIME" + "\n";
                sourcetableSql += "    ,'admin'" + " AS UPDATOR" + "\n";
                sourcetableSql += "    ,to_char(systimestamp,'YYYYMMDDHH24miss')" + " AS UPDATE_TIME" + "\n";
                sourcetableSql += "from \"" + source.getSourceDbtableName() + "\"";
                sourcetableSql = sourcetableSql.replace("\"UUID()\"", "sys_guid()").replace("\"%\"","''");
                source.setSourcetableSql(sourcetableSql);
                source.getCreateTime();

                Map<String, Object> root = new HashMap<String, Object>();
                root.put("source", source);
                root.put("keyMap", keyMap);
                root.put("columnValueList", columnValueList);

                String ktrName = "同步" + source.getSourceDbtableName() + "到" + source.getDestinationDbtableName() + ".ktr";
                int lastNum = filePath.lastIndexOf("\\");
                String savePath = filePath.substring(0, lastNum + 1);
                fmTemplate.fprint("ktr1.1.ftl", root, ktrName, savePath);
            }
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                pkg.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入配置档路径：");
        String filePath = scanner.nextLine();
        ReadExcelXlsx readExcelXlsx = new ReadExcelXlsx();
        readExcelXlsx.readData(filePath);
    }
}
